from app.services.business.gaolu_diagnose.ind.abstract_ind import AbstractInd
from typing import List


class CG_LT_GL_GL04_RR2(AbstractInd):

    def get_name(self):
        return "CG_LT_GL_GL04_RR2"

    def get_data_source(self):
        return "mysql"

    def get_sql(self, params) -> list:
        sql_cao = """      
        select round(avg(jyh.jyjg),4) as CG_LT_GL_GL04_RCaO from 
syn_tq_rgaoluchutie,(select jyh.*,jyt.ph 
from syn_zl_zl_jyh as jyh ,syn_zl_zl_jyt as jyt 
where  jyh.jcxmbm='01010' and jyh.zfbz='0' 
and jyt.wlbm = 'ZJ003' 
and SUBSTRING(jyt.ph, 4, 1) = '4' 
and  jyt.jytid = jyh.jytid 
and jyh.jyjg NOT LIKE '%#%' 
and jyh.jysj > DATE_SUB(CURDATE(), INTERVAL 1 WEEK)) as jyh 
where  SUBSTRING(jyh.ph,2,7) = concat(SUBSTRING(syn_tq_rgaoluchutie.fdate,3,2),'4',syn_tq_rgaoluchutie.fnumber) and syn_tq_rgaoluchutie.fdatetime > '{dt_start}'
        """.format(**params)

        sql_sio2 = """
        select round(avg(jyh.jyjg),4) as CG_LT_GL_GL04_RSiO2 from 
syn_tq_rgaoluchutie,(select jyh.*,jyt.ph 
from syn_zl_zl_jyh as jyh ,syn_zl_zl_jyt as jyt 
where  jyh.jcxmbm='01011' and jyh.zfbz='0' 
and jyt.wlbm = 'ZJ003' 
and SUBSTRING(jyt.ph, 4, 1) = '4' 
and  jyt.jytid = jyh.jytid 
and jyh.jyjg NOT LIKE '%#%' 
and jyh.jysj > DATE_SUB(CURDATE(), INTERVAL 1 WEEK)) as jyh 
where  SUBSTRING(jyh.ph,2,7) = concat(SUBSTRING(syn_tq_rgaoluchutie.fdate,3,2),'4',syn_tq_rgaoluchutie.fnumber) and syn_tq_rgaoluchutie.fdatetime > '{dt_start}'
        """.format(**params)

        return [sql_cao, sql_sio2]

    @AbstractInd.cache_wrapper
    def get_result(self):
        sql_list: List[str] = self.get_sql(self.params)
        cursor = self.mysql_cursor

        cao = sql_list[0]
        cursor.execute(cao)
        cao = cursor.fetchone()

        cao = cao['CG_LT_GL_GL04_RCaO'] if cao is not None and \
                                           cao['CG_LT_GL_GL04_RCaO'] is not None else 0

        sio2 = sql_list[1]
        cursor.execute(sio2)
        sio2 = cursor.fetchone()
        sio2 = sio2['CG_LT_GL_GL04_RSiO2'] if sio2 is not None and \
                                              sio2['CG_LT_GL_GL04_RSiO2'] is not None else 0

        if cao == 0 or sio2 == 0: return 0

        return round(cao / sio2, 2)
